package org.emet.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.TreeMap;

import org.emet.Constants;
import org.emet.entity.biz.ChargeRecord;
import org.emet.jee.core.dao.Page;
import org.emet.jee.core.dao.arch.ArchDataSourceBaseDao;
import org.emet.service.dto.ChargeRecordCriteria;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class ChargeRecordDao extends ArchDataSourceBaseDao<ChargeRecord, Long> {
	public Page<ChargeRecord> search(Page<ChargeRecord> page, ChargeRecordCriteria criteria) {
		Criteria c = getSession().createCriteria(ChargeRecord.class);
		NonEmptyValueHashMap<String, Object> propertyNameValues = new NonEmptyValueHashMap<String, Object>();
		propertyNameValues.put("registration.bizNo", criteria.getBizNo());
		propertyNameValues.put("totalFee", criteria.getTotalFee());
		propertyNameValues.put("shouldPaidTotalFee", criteria.getShouldPaidTotalFee());
		propertyNameValues.put("paidFee", criteria.getPaidFee());
		propertyNameValues.put("chargeDate", criteria.getChargeDate());
		propertyNameValues.put("operator", criteria.getOperator());

		if (Constants.CHARGED.equals(criteria.getChargeType())) {
			c.add(Restrictions.sqlRestriction("(should_Paid_Total_Fee-paid_Fee) <= 0 "));
		} else if (Constants.NON_CHARGE.equals(criteria.getChargeType())) {
			c.add(Restrictions.sqlRestriction("(should_Paid_Total_Fee-paid_Fee) > 0 "));
		}
		c.add(Restrictions.allEq(propertyNameValues));
		page.setOrderBy("id");
		page.setOrder("desc");
		return findPage(page, c);
	}

	public List<TreeMap<String, Object>> findGenerateBizNo(int year, int mon) {
		String sql = "SELECT DAY(CHARGE_DATE) AS DAYSTR, SUM(PAID_FEE) AS PAIDFEE, SHOULD_PAID_TOTAL_FEE,SUM(SHOULD_PAID_TOTAL_FEE-PAID_FEE) AS OWNFEE FROM T_CHARGE_REC WHERE YEAR(CHARGE_DATE)=? AND MONTH(CHARGE_DATE)=? GROUP BY DAY(CHARGE_DATE)";
		return getJdbcTemplate().query(sql, rm, year, mon);
	}

	public List<Integer> countBodyCountGroupByMonth(Integer year, Integer month) {
		String sql = "SELECT COUNT(1) FROM (SELECT DISTINCT DAY(CHARGE_DATE) AS DATEOFMON, REG_ID FROM T_CHARGE_REC AS REC LEFT JOIN T_CHARGE_ITEM AS ITEM ON REC.ID = ITEM.REC_ID AND YEAR(CHARGE_DATE ) = ? AND MONTH(CHARGE_DATE) = ?) AS T GROUP BY t.dateofmon order by t.DATEOFMON asc";
		return getJdbcTemplate().query(sql, rm2, year, month);
	}

	public List<HashMap<String, Object>> findServiceItemOfDay(Date date) {
		String sql = "SELECT DETAIL_NAME,SUM(TOTAL_FEE) FROM T_CHARGE_ITEM WHERE REC_ID IN (SELECT ID AS RECID FROM T_CHARGE_REC AS REC WHERE DATE(CHARGE_DATE) = ? ) GROUP BY DETAIL_NAME";
		return getJdbcTemplate().query(sql, rm3, new java.sql.Date(date.getTime()));
	}

	ParameterizedRowMapper<HashMap<String, Object>> rm3 = new ParameterizedRowMapper<HashMap<String, Object>>() {

		public HashMap<String, Object> mapRow(ResultSet rs, int arg1) throws SQLException {
			HashMap<String, Object> hashMap = new HashMap<String, Object>();
			hashMap.put("name", rs.getString(1));
			hashMap.put("totalFee", rs.getDouble(2));
			return hashMap;
		}

	};

	ParameterizedRowMapper<Integer> rm2 = new ParameterizedRowMapper<Integer>() {
		public Integer mapRow(ResultSet rs, int arg1) throws SQLException {
			return rs.getInt(1);
		}
	};

	ParameterizedRowMapper<TreeMap<String, Object>> rm = new ParameterizedRowMapper<TreeMap<String, Object>>() {
		public TreeMap<String, Object> mapRow(ResultSet rs, int arg1) throws SQLException {
			TreeMap<String, Object> map = new TreeMap<String, Object>();
			map.put("dayStr", rs.getInt("dayStr"));
			map.put("paidFee", rs.getDouble("paidFee"));
			map.put("should_paid_total_fee", rs.getDouble("should_paid_total_fee"));
			map.put("ownFee", rs.getDouble("ownFee"));
			return map;
		}
	};
}
